package utils;

import java.sql.*;
import org.json.*;

public class SqlJsonConvertor {
    public static void main(String[] args) {
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://192.168.3.248:3306/productdb";
        String user = "iplastest";
        String passwd = "123456";
        String sql = "select * from cities";

        try {
            Class.forName(driver);
            Connection con = DriverManager.getConnection(url, user, passwd);
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
            int colnum = rsmd.getColumnCount();
            String val = "";
            String colName = "";
            JSONObject jobj = new JSONObject();
            JSONArray jArr = new JSONArray();

            while (rs.next()) {
                for (int i = 1; i <= colnum; i++) {
                    colName = rsmd.getColumnLabel(i);
                    if (1 == i) {
                        val = new Integer(rs.getInt(colName)).toString();
                    } else {
                        val = rs.getString(colName);
                    }

                    try {
                        jobj.put(colName, val);

                    } catch (JSONException e) {
                        e.printStackTrace();
                    }
                }
                jArr.put(jobj);
            }
            System.out.println("Here is the json String:");
            System.out.println(jArr.toString());

        } catch (ClassNotFoundException e) {
            System.out.println("Driver not found");
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
